from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Float, BigInteger, DateTime
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:shishen112@127.0.0.1:3306/yunmusic?charset=utf8", encoding='utf-8')

Base = declarative_base()
Session = sessionmaker(bind=engine)


class Playlist(Base):
    __tablename__ = 'playlist'
    id = Column(String(20), primary_key=True)
    user_id = Column(String(20))
    name = Column(String(255))
    description = Column(String(255))
    play_count = Column(Integer)
    cover_img_id = Column(String(20))
    update_time = Column(DateTime)
    create_time = Column(DateTime)


class User(Base):
    __tablename__ = 'user'
    id = Column(String(20), primary_key=True)
    nickname = Column(String(255))
    password = Column(String(255))
    description = Column(String(255))
    avatar_img_id = Column(String(255))
    user_type = Column(Integer)


class Song(Base):
    __tablename__ = 'song'
    id = Column(String(20), primary_key=True)
    name = Column(String(255))
    song_url_id = Column(String(255))
    publish_time = Column(DateTime)
    lyric = Column(String(65535))
    duration = Column(Integer)


class Comment(Base):
    __tablename__ = 'comment'
    id = Column(String(20), primary_key=True)
    user_id = Column(String(20))
    target_id = Column(String(20))
    type = Column(Integer)
    reply_comment_id = Column(String(20))
    content = Column(String(255))
    create_time = Column(DateTime)


class Artist(Base):
    __tablename__ = 'artist'
    id = Column(String(20), primary_key=True)
    name = Column(String(255))
    description = Column(String(255))
    pic_url_id = Column(String(255))


class Album(Base):
    __tablename__ = 'album'
    id = Column(String(20), primary_key=True)
    name = Column(String(255))
    description = Column(String(255))
    pic_url_id = Column(String(255))
    publish_time = Column(DateTime)


class PlaylistTag(Base):
    __tablename__ = 'playlist_tag'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))


class ArtistTag(Base):
    __tablename__ = 'artist_tag'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))


class AlbumHasSong(Base):
    __tablename__ = 'album_has_song'
    id = Column(String(20), primary_key=True)
    song_id = Column(String(20))
    album_id = Column(String(20))


class ArtistHasAlbum(Base):
    __tablename__ = 'artist_has_album'
    id = Column(String(20), primary_key=True)
    artist_id = Column(String(20))
    album_id = Column(String(20))


class PlaylistHasSong(Base):
    __tablename__ = 'playlist_has_song'
    id = Column(String(20), primary_key=True)
    playlist_id = Column(String(20))
    song_id = Column(String(20))


def set_song_duration(song_id, duration):
    session = Session()
    try:
        session.query(Song).filter(Song.id == song_id).update({Song.duration: duration})
        session.commit()
    finally:
        session.close()


def add(object):
    session = Session()
    try:
        session.add(object)
        session.commit()
    finally:
        session.close()


def exist_comment(user_id, content):
    session = Session()
    try:
        ret = session.query(Comment).filter_by(user_id=user_id) \
            .filter_by(content=content).all()
        return len(ret) > 0
    finally:
        session.close()


def exist_album_has_song(album_id, song_id):
    session = Session()
    try:
        ret = session.query(AlbumHasSong).filter_by(album_id=album_id)\
            .filter_by(song_id=song_id).all()
        return len(ret) > 0
    finally:
        session.close()


def exist_artist_has_album(artist_id, album_id):
    session = Session()
    try:
        ret = session.query(ArtistHasAlbum).filter_by(artist_id=artist_id)\
            .filter_by(album_id=album_id).all()
        return len(ret) > 0
    finally:
        session.close()


def exist_playlist_has_song(playlist_id, song_id):
    session = Session()
    try:
        ret = session.query(PlaylistHasSong).filter_by(playlist_id=playlist_id)\
            .filter_by(song_id=song_id).all()
        return len(ret) > 0
    finally:
        session.close()


def exist(id, table):
    session = Session()
    try:
        ret = session.query(table).filter_by(id=id).all()
        return len(ret) > 0
    finally:
        session.close()


def query_all(table):
    session = Session()
    try:
        ret = session.query(table).all()
        return ret
    finally:
        session.close()


def add_playlist(playlist):
    session = Session()
    try:
        session.add(playlist)
        session.commit()
    finally:
        session.close()


def exist_playlist(id):
    session = Session()
    try:
        ret = session.query(Playlist).filter_by(id=id).all()
        return len(ret) > 0
    finally:
        session.close()

